﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SundUnderviser.ModelLayer;

namespace SundUnderviser.DBLayer
{
     public class DBLektionsFamilie
    {
        private SqlCommand dbCmd = null;

        public Familie FindFamilie( int pnr)
        {
            string sql = @"select nr, navn, adresse, postnr, byen, telefon, relation from Lektionsfamilie " +
                           " where patient  = " + pnr;
                       

            Console.WriteLine("sql stmt = " + sql);

            dbCmd = DBConnection.GetDbCommand(sql);

            IDataReader dbReader;
            dbReader = dbCmd.ExecuteReader();

            Familie famObj = new Familie();

            if (dbReader.Read())
            {
                try
                {
                    Console.WriteLine("antal felter " + dbReader.FieldCount);
                    famObj.Navn = dbReader["navn"].ToString();
                    famObj.Nr = Convert.ToInt32(dbReader["nr"].ToString());
                    famObj.Adresse = dbReader["adresse"].ToString();
                    famObj.Relation = dbReader["relation"].ToString();

                }
                catch (Exception e)
                {
                    Console.WriteLine("LektionsFamilie objekt ikke oprettet korrekt");
                    famObj = null;
                }


            }

            else
            {
                famObj = null;
            }
            dbReader.Close();
            DBConnection.Close();
            return famObj;
        }


        
        public int InsertFamilie(Familie famObj)
        {
            int rc = -1;
            SqlCommand dbCmd = null;
            try
            { // tildel naeste nr
                String sql = "select max(nr) as nextnr from Lektionsfamilie";
                SqlCommand dbCmd1 = DBConnection.GetDbCommand(sql);
                IDataReader dbReader;
                dbReader = dbCmd1.ExecuteReader();
                if (dbReader.Read())
                {
                    try
                    {
                        famObj.Nr = Convert.ToInt32(dbReader["nextnr"].ToString()) + 1;
                        Console.WriteLine("næstenr familieObj: " + famObj.Nr);

                    }
                    catch (Exception exobj)
                    {
                        famObj.Nr = 1;
                    }
                }
                DBConnection.Close();
                Console.WriteLine("næstenr familieObj: " + famObj.Nr);

          

                string sql1 = " insert into Lektionsfamilie (nr, navn,adresse, postnr, byen, telefon,relation,patient)" +
                       "values (" +
                        famObj.Nr + ",'" +
                        famObj.Navn + "','" +
                        famObj.Adresse + "','" +
                        famObj.Byen + "','" +
                        famObj.Telefon + "','" +
                        famObj.Relation + "','" +
                        famObj.Patient.Nr + "')";
                Console.WriteLine("familie insert " + sql1);
                dbCmd = DBConnection.GetDbCommand(sql1);


               
                rc = dbCmd.ExecuteNonQuery();


            }
            catch (Exception insertE)
            {
                Console.WriteLine("Fejl i insert lektionsfamilie");
                Console.WriteLine(insertE);
            }
            DBConnection.Close();
            return rc;
        }
    }
}
